﻿<%@ WebHandler Language="C#" Class="ZSFanJianYearData" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Web.SessionState;
using System.IO;
using ReportBusiness;
public class ZSFanJianYearData : IHttpHandler
{
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        //本年各月统计
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetMonthData")
        {
            string sql = @"
SELECT t.planmonth || '月' planmonth,COUNT(1) total,SUM(CASE WHEN t.finishtime IS NULL THEN 1 ELSE 0 END) inprocessqty,
       SUM(CASE WHEN t.delaydays > 0 THEN 1 ELSE 0 END) delayqty
FROM (
    SELECT to_char(db.createdate,'mm') planmonth,db.dealfaultbillname,db.engineno,db.productname,db.productbatchno,f.factoryname 接收单位,
           CASE WHEN db.transferstatus = 2 AND dbt.revrejectreason IS NOT NULL THEN '接收退回'
                WHEN db.transferstatus = 5 AND dbt.revreturnrejectreason IS NOT NULL THEN '返回退回'
                WHEN db.transferstatus = 2 THEN '待返出'
                WHEN db.transferstatus = 3 THEN '待接收'
                WHEN db.transferstatus = 4 THEN '待完成'
                WHEN db.transferstatus = 5 THEN '待返回'
           END status,
           dbt.FromTime,dbt.RequireFinishTime,dbt.RecvFactoryTime,dbt.estimatedcompletedate,dbt.finishtime,
           CASE WHEN dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NOT NULL
                     THEN TRUNC(to_date(to_char(dbt.finishtime,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd'))
                WHEN dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NULL
                     THEN TRUNC(to_date(to_char(SYSDATE,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd'))
           ELSE 0 END delaydays,w.workcentername
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN Sessionvalues sv ON sv.employeeid = db.createemployeeid
    LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
    LEFT JOIN factory f ON f.factoryid = sv.factoryid
    WHERE f.factoryname = '装配试车厂'
          AND w.workcentername IS NOT NULL
          AND extract(year from db.createdate) = extract(year from SYSDATE)
) t
GROUP BY t.planmonth
ORDER BY t.planmonth
";

            DataTable dt = dal.GetDataTable(sql);
            var json = JsonConvert.SerializeObject(dt);
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }

        //本年各工区统计
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetWorkcenterData")
        {
            string sql = @"
SELECT t.workcentername,COUNT(1) total,SUM(CASE WHEN t.finishtime IS NULL THEN 1 ELSE 0 END) inprocessqty,
       SUM(CASE WHEN t.delaydays > 0 THEN 1 ELSE 0 END) delayqty
FROM (
    SELECT db.dealfaultbillname,db.engineno,db.productname,db.productbatchno,f.factoryname 接收单位,
           CASE WHEN db.transferstatus = 2 AND dbt.revrejectreason IS NOT NULL THEN '接收退回'
                WHEN db.transferstatus = 5 AND dbt.revreturnrejectreason IS NOT NULL THEN '返回退回'
                WHEN db.transferstatus = 2 THEN '待返出'
                WHEN db.transferstatus = 3 THEN '待接收'
                WHEN db.transferstatus = 4 THEN '待完成'
                WHEN db.transferstatus = 5 THEN '待返回'
           END status,
           dbt.FromTime,dbt.RequireFinishTime,dbt.RecvFactoryTime,dbt.estimatedcompletedate,dbt.finishtime,
           CASE WHEN dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NOT NULL
                     THEN TRUNC(to_date(to_char(dbt.finishtime,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd'))
                WHEN dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NULL
                     THEN TRUNC(to_date(to_char(SYSDATE,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd'))
           ELSE 0 END delaydays,REPLACE(w.workcentername,'装试','') workcentername
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN Sessionvalues sv ON sv.employeeid = db.createemployeeid
    LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
    LEFT JOIN factory f ON f.factoryid = sv.factoryid
    WHERE f.factoryname = '装配试车厂'
          AND w.workcentername IS NOT NULL
          AND extract(year from db.createdate) = extract(year from SYSDATE)
) t
GROUP BY t.workcentername
";
            DataTable dt = dal.GetDataTable(sql);
            var json = JsonConvert.SerializeObject(dt);
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }
    }
}